﻿using AntdUI;
using CKS.Core.Framework;
using FreeSql;
using Infrastructure.Extensions;
using Microsoft.AspNetCore.Mvc;
using Microsoft.IdentityModel.Tokens;
using MiniExcelLibs;
using MiniExcelLibs.Attributes;
using MiniExcelLibs.OpenXml;
using MySqlX.XDevAPI.Relational;
using Newtonsoft.Json.Linq;
using S7.Net;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Drawing.Printing;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using ZR.Admin.WebApi.Filters;
using ZR.Infrastructure;
using ZR.Model.Dto.Business.ProductionControl;
using ZR.Model.Models.ProductionControl;
using ZR.Repository;
using ZR.Service.IService.ProductionControl;
using ZR.Service.Service.ProductionControl;

namespace CKS.MES.Server.Controllers.Business.ProductionControl {
    /// <summary>
    /// 数据采集记录（追溯）
    /// </summary>
    [Verify]
    [Route("business/CksDataRecord")]
    [ApiExplorerSettings(GroupName = "business")]
    public class CksDataRecordController : BaseController {

        private readonly IFreeSql _freeSqlClient; //= InternalApp.Fsql;

        public CksDataRecordController(IFreeSql<MesDB> freeSql) {
            _freeSqlClient = freeSql;
        }

        /// <summary>
        /// 获取虚拟表列表
        /// </summary>
        /// <returns></returns>
        [HttpGet("getVirtualTables")]
        public IActionResult GetVirtualTables() {
            var response = _freeSqlClient.Queryable<CksVirtualTable>().Where(o => !o.IsDeleted).OrderBy(a => a.Sequence).ToList();
            return SUCCESS(response);
        }

        /// <summary>
        /// 数据追溯查询，返回列头信息 和 表格数据
        /// </summary>
        /// <param name="parm"></param>
        /// <returns></returns>
        [HttpPost("getCollectDetailsData/{tableName}")]
        [AllowAnonymous]
        public IActionResult QueryCollectData([FromBody] PlcConfigQueryDto parm, [FromRoute] string tableName) {
            Stopwatch stopwatch = Stopwatch.StartNew();
            var lang = Configs.GetValue("Lang");
            using (var ObjectCo = _freeSqlClient.Ado.MasterPool.Get()) {
                var conn = ObjectCo.Value;

                var tableExists = _freeSqlClient.DbFirst.GetTablesByDatabase().Exists(t => t.Name == tableName);
                //如果表不存在则继续循环
                if (!tableExists) {
                    return ToResponse(ResultCode.FAIL, "未查询到相关表，请重试！");
                }

                var table = conn.Select<CksVirtualTable>().Where(a => a.TableName == tableName && !a.IsDeleted).First();
                if (table == null) {
                    return ToResponse(ResultCode.FAIL, "未查询到相关表，请重试！");
                }

                var columnQuery = conn.Select<CksVirtualColumn>().Where(a => a.TableId == table.Id && a.IsDisplay).OrderBy(a => a.Sequence);
                var columns = columnQuery.ToList();

                var page = parm.Limit > 0 ? 1 : parm.PageNum;
                var pageSize = parm.Limit > 0 ? parm.Limit : parm.PageSize;
                if (parm.EndTime == null) {
                    parm.EndTime = DateTime.Now;
                }

                var results = _freeSqlClient.Queryable<object>()
                    .AsTable((type, oldName) => { return table.TableName; })
                    .LeftJoin("cks_plcblockdb b on a.PlcId= b.PlcId and a.BlockDb = b.BlockDb")
                    .WhereIf(!parm.PrimaryCode.IsNullOrEmpty(), " a.MainCode ='" + parm.PrimaryCode + "' ")
                    .WhereIf(parm.BeginTime != null, $" a.CreateTime Between '{parm.BeginTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' AND '{parm.EndTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ")
                    .OrderBy(" a.CreateTime desc ")
                    .Page(page, pageSize)
                    .ToDataTable("a.*,b.remark");

                columns.Insert(0,new CksVirtualColumn() { ColumnName = "id", Notes = "ID", Width = 80, IsDisplay = true, LanguageKey = "" });
                if (HttpContext.IsAdmin()) {
                    columns.Insert(1, new CksVirtualColumn() { ColumnName = "collectBatchCode", Notes = "采集批次", Width = 0, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.collectionBatch" });
                }
                //组装数据
                columns.ForEach(o => {
                    o.ColumnName = new string(o.ColumnName.Select((c, i) => i == 0 ? char.ToLower(c) : c).ToArray());
                    //统一处理 主码、节拍、结果 翻译Key
                    var col = o;
                    if (col.ColumnName.ToLower() == "maincode") {
                        col.LanguageKey = "review.cksdatarecordsPage.collectionColumns.paimaryCode";
                    } else if (col.ColumnName.ToLower() == "cycletime") {
                        col.LanguageKey = "review.cksdatarecordsPage.collectionColumns.cycleTime";
                    } else if (col.ColumnName.ToLower() == "result") {
                        col.LanguageKey = "review.cksdatarecordsPage.collectionColumns.result";
                    }
                });
                //通用字段
                columns.Add(new CksVirtualColumn() { ColumnName = "startTime", Notes = "开始时间", Width = 0, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.startTime" });
                columns.Add(new CksVirtualColumn() { ColumnName = "endTime", Notes = "结束时间", Width = 0, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.endTime" });
                columns.Add(new CksVirtualColumn() { ColumnName = "remark", Notes = "备注", Width = 0, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.remark" });
                columns.Add(new CksVirtualColumn() { ColumnName = "createTime", Notes = "创建时间", Width = 0, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.createdTime" });

                foreach (DataColumn item in results.Columns) {
                    item.ColumnName = new string(item.ColumnName.Select((c, i) => i == 0 ? char.ToLower(c) : c).ToArray());
                }

                JObject keys = new JObject();
                keys.Add("queryTime", $"{(int)stopwatch.Elapsed.TotalMilliseconds}ms");
                keys.Add("data", JArray.FromObject(results));
                keys.Add("columns", JArray.FromObject(columns.Select(a => new { columnName = a.ColumnName, notes = a.Notes, languageKey = a.LanguageKey, width = a.Width, isDisplay = a.IsDisplay })));
                keys.Add("pagination", JObject.FromObject(new { total = results.Rows.Count }));
                
                stopwatch.Stop();//停止监视耗时
                return SUCCESS(keys);

                #region Old
                /*
                int TableId = table.Id;
                var columnQuery = conn.Select<CksVirtualColumn>().Where(a => a.TableId == TableId && a.IsDisplay).OrderBy(a => a.Sequence);
                var columns = columnQuery.ToList();

                //查询虚拟表格数据
                var page = parm.Limit > 0 ? 1 : parm.PageNum;
                var pageSize = parm.Limit > 0 ? parm.Limit : parm.PageSize;

                if (parm.Limit > 10000) {
                    return ToResponse(ResultCode.FAIL, "Limit Size 最大 10000");
                }

                // 1. 先获取符合条件的批次数据（按时间范围过滤）
                var batchQuery = conn.Select<CksCollected>()
                    .Where(x => x.TableId == table.Id)
                    .WhereIf(!parm.PrimaryCode.IsNullOrEmpty() && parm.PrimaryCode.Length >= 4, x => x.Suffix_4 == GetSuffix4(parm.PrimaryCode))
                    .WhereIf(!parm.PrimaryCode.IsNullOrEmpty(), x => x.PrimaryCode.EndsWith(parm.PrimaryCode))
                    .WhereIf(parm.Limit > 0, x => x.SequenceId >= parm.StartSeq)
                    .WhereIf(parm.BeginTime != null, x => x.CreatedTime.Between(parm.BeginTime.Value, parm.EndTime.Value))
                    .OrderByIf(parm.Limit > 0, x => x.SequenceId)
                    .OrderByDescending(parm.Limit == 0, x => x.SequenceId)
                    .Page(page, pageSize)
                    .OrderByDescending(parm.Limit > 0, x => x.SequenceId);

                var total = batchQuery.Count();

                var dbBlock = conn.Select<CksPlcBlockDb>();

                // 2. 获取这些批次的所有明细数据（一次性加载）
                var details = conn.Select<CksCollectedDetails>()
                            .FromQuery(batchQuery, columnQuery, dbBlock)
                            .InnerJoin((a, b, c, d) => a.TableId == b.TableId && a.BatchSuffix == b.BatchSuffix && a.BatchCode == b.BatchCode)
                            .InnerJoin((a, b, c, d) => a.TableId == c.TableId && a.ColumnId == c.Id)
                            .LeftJoin((a, b, c, d) => b.PlcId == d.PlcId && b.ReadDB == d.BlockDb)
                            .ToList(
                                (a, b, c, d) => new { c.ColumnName, b.CreatedTime, b.SequenceId, b.BatchCode, a.Content, d.Remark }
                            );

                if (HttpContext.IsAdmin()) {
                    columns.Insert(0, new CksVirtualColumn() { ColumnName = "batchId", Notes = "采集批次", Width = 0, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.collectionBatch" });
                    columns.Insert(1, new CksVirtualColumn() { ColumnName = "sequenceId", Notes = "采集编号", Width = 160, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.collectionNumber" });
                } else {
                    columns.Insert(0, new CksVirtualColumn() { ColumnName = "sequenceId", Notes = "ID", Width = 90, IsDisplay = true, LanguageKey = "" });
                }

                columns.ForEach(o => {
                    o.ColumnName = new string(o.ColumnName.Select((c, i) => i == 0 ? char.ToLower(c) : c).ToArray());
                    //统一处理 主码、节拍、结果
                    var col = o;
                    if (col.ColumnName.ToLower() == "maincode") {
                        col.LanguageKey = "review.cksdatarecordsPage.collectionColumns.paimaryCode";
                    } else if (col.ColumnName.ToLower() == "cycletime") {
                        col.LanguageKey = "review.cksdatarecordsPage.collectionColumns.cycleTime";
                    } else if (col.ColumnName.ToLower() == "result") {
                        col.LanguageKey = "review.cksdatarecordsPage.collectionColumns.result";
                    }
                });
                columns.Add(new CksVirtualColumn() { ColumnName = "remark", Notes = "备注", Width = 0, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.remark" });
                columns.Add(new CksVirtualColumn() { ColumnName = "createdTime", Notes = "创建时间", Width = 0, IsDisplay = true, LanguageKey = "review.cksdatarecordsPage.collectionColumns.createdTime" });

                // 3. 组装数据
                var result = details.Select(d => new {
                    d.BatchCode,
                    d.ColumnName,
                    d.Content,
                    d.CreatedTime,
                    d.SequenceId,
                    d.Remark
                })
                .GroupBy(x => new { x.BatchCode, x.CreatedTime, x.SequenceId, x.Remark })
                .Select(g => {
                    var dict = new Dictionary<string, object>();
                    dict["batchId"] = g.Key.BatchCode;
                    dict["sequenceId"] = g.Key.SequenceId;
                    //dict["CollectionTime"] = g.Key.CollectionTime;

                    //遍历列头，为空则赋值空
                    foreach (var col in columns) {
                        var item = g.ToList().Find(a => a.ColumnName.ToLower() == col.ColumnName.ToLower());
                        if (item != null) {
                            dict[col.ColumnName] = item.Content;
                        }
                    }

                    dict["remark"] = g.Key.Remark;
                    dict["createdTime"] = g.Key.CreatedTime;
                    return dict;
                })
                .OrderByDescending(x => x["sequenceId"])
                .ToList();

                object rcolumns = null;

                if (parm.Limit > 0)
                    rcolumns = columns.Select(a => new { a.ColumnName, a.Notes });
                else
                    rcolumns = columns.Select(a => new { a.ColumnName, a.Notes, a.LanguageKey, a.Width, a.IsDisplay });

                stopwatch.Stop();//停止监视耗时

                return SUCCESS(new { queryTime = $"{(int)stopwatch.Elapsed.TotalMilliseconds}ms", data = result, columns = rcolumns, Pagination = new { Total = total } });
            //*/
                #endregion
            }
        }


        [HttpPost("getCurveData/{collectedBatchCode}/{primaryCode}")]
        public IActionResult QueryCurveData([FromRoute] string collectedBatchCode, [FromRoute] string primaryCode) {

            var data = _freeSqlClient.Select<CksCollectedCurveData>()
              .Where(o => o.PrimaryCode == primaryCode && o.CollectBatchCode == collectedBatchCode)
              .ToList().Select(o => JObject.Parse(o.CurveData));

            return SUCCESS(data);
        }


        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="parm"></param>
        /// <returns></returns>
        [HttpGet("ExportCollectData")]
        public IActionResult ExportCollectData([FromQuery] PlcConfigQueryDto parm) {

            using (var ObjectCo = _freeSqlClient.Ado.MasterPool.Get()) {
                var conn = ObjectCo.Value;

                var exportDataSet = new DataSet();

                //获取所有的虚拟表
                var tables = conn.Select<CksVirtualTable>().Where(o => !o.IsDeleted).OrderBy(a => a.Sequence).ToList();
                
                foreach (var table in tables) {

                    var tableExists = _freeSqlClient.DbFirst.GetTablesByDatabase().Exists(t => t.Name == table.TableName);
                    //如果表不存在则继续循环
                    if (!tableExists) {
                        continue;
                    }

                    //查询列头信息，输出到sheet
                    var columnQuery = conn.Select<CksVirtualColumn>().Where(a => a.TableId == table.Id && a.IsDisplay).OrderBy(a => a.Sequence);
                    var columns = columnQuery.ToList();
                    
                    //查询表数据
                    var results = _freeSqlClient.Queryable<object>()
                          .AsTable((type, oldName) => { return table.TableName; })
                          .LeftJoin("cks_plcblockdb b on a.PlcId= b.PlcId and a.BlockDb = b.BlockDb")
                          .WhereIf(!parm.PrimaryCode.IsNullOrEmpty(), " a.MainCode ='" + parm.PrimaryCode + "' ")
                          .WhereIf(parm.BeginTime != null, $" a.CreateTime Between '{parm.BeginTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' AND '{parm.EndTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ")
                          .OrderBy(" a.CreateTime desc ")
                          .Take(100000)
                          .ToDataTable("a.*,b.remark");

                    // 1. 复制DataTable（保持原数据不变）
                    var dataTable = results.Copy();
                    dataTable.TableName = table.TableName;

                    // 2. 预定义列名映射（固定列名的中文对照，优化查找效率）
                    var fixedColumnMappings = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
                    {
                        { "ID", "ID" },
                        { "StartTime", "开始时间" },
                        { "EndTime", "结束时间" },
                        { "CreateTime", "创建时间" },
                        { "Remark", "备注" }
                    };

                    // 3. 预定义需要保留的列（避免后续误删）
                    var columnsToKeep = new List<DataColumn>();

                    // 4. 遍历列，先处理重命名，再收集需要保留的列
                    foreach (DataColumn column in dataTable.Columns) {
                        string originalColName = column.ColumnName;
                        bool isColumnKept = false;

                        // 4.1 优先匹配自定义列（columns集合中的Notes）
                        var matchedCol = columns.FirstOrDefault(o =>
                            string.Equals(o.ColumnName, originalColName, StringComparison.OrdinalIgnoreCase));
                        if (matchedCol != null && !string.IsNullOrEmpty(matchedCol.Notes)) {
                            column.ColumnName = matchedCol.Notes; // 重命名为Notes
                            columnsToKeep.Add(column);
                            isColumnKept = true;
                        }
                        // 4.2 再匹配固定列名
                        else if (fixedColumnMappings.TryGetValue(originalColName, out string chineseName)) {
                            column.ColumnName = chineseName; // 重命名为固定中文
                            columnsToKeep.Add(column);
                            isColumnKept = true;
                        }

                        // 4.3 不保留的列不加入columnsToKeep（后续统一删除）
                    }

                    // 5. 统一删除不需要保留的列（关键：避免遍历集合时修改）
                    // 反向遍历Columns，避免删除时索引错乱
                    for (int i = dataTable.Columns.Count - 1; i >= 0; i--) {
                        DataColumn column = dataTable.Columns[i];
                        if (!columnsToKeep.Contains(column)) {
                            dataTable.Columns.Remove(column);
                        }
                    }

                    // 6. 添加到导出列表
                    exportDataSet.Tables.Add(dataTable);
                }

                var stream = new MemoryStream();
                stream.SaveAs(exportDataSet);
                stream.Seek(0, SeekOrigin.Begin);

                Response.Headers.Append("Access-Control-Expose-Headers", "Content-Disposition");
                return File(stream,
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    HttpUtility.UrlEncode($"MES-Export-{DateTime.Now.ToString("MMddHHmmss")}.xlsx"));
            }
        }

    }

}
